#!/use/bin/python
# coding=utf-8
# 数据分析-主动服务-留存量 7日/15日/30日  (无需执行一次) analysis_retention_number
#KEY
#V2为：无需执行一次 V1为：需要执行一次 V3为因为弹窗新增的
# LOG_DIVICE_ACTIVE_V2_* 为服务当前总订阅量  LOG_DIVICE_NEW_ACTIVE_V2_* 为服务当天订阅量
import redis
import datetime
import pymysql

from dbutils.pooled_db import PooledDB

# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# redis
def getReidsConnection():
    redis_info = {
        "host": "172.20.151.90",
        "password": "Coocaa2022",
        "port": 6379,
        "db": 8
    }

    r = redis.Redis(**redis_info, decode_responses=True)
    return r

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql):
    conn, cur = getDataConnection()
    print(sql)
    cur.execute(sql)
    numResult = cur.fetchone()
    num = 0
    if numResult is not None:
        num = numResult[0]
    return num
    return countNum

# 记录 7日留存率 - type3
#serId 服务 executeFlag 是否执行 1是 0否
def saveRetentionNum(date,serId,executeFlag,day):
    try:
        if executeFlag == 0:
            #redis key
            redisStr = 'V2'
            logStr = '无需执行一次'
        else:
            redisStr = 'V1'
            logStr = '执行一次'

        conn, cur = getDataConnection()
        r = getReidsConnection()

        #获取7/15/30留存量
        beforeDay = strToTime(date, -day)
        retentionKey = f"LOG_DIVICE_ACTIVE_{redisStr}_RETENTION{day}_{serId.upper()}"
        retentionKeyNum = r.scard(retentionKey);

        serName = serIdToName(serId.upper())
        # 判断 analysis_zjtx_retention_rate 表是否已经存在当天 数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_retention_number t where 1=1 and t.ref_date =  '{beforeDay}' and t.service_key = '{serId}' and execute_flag = {executeFlag} ")
        if countNum == 0:
            logDetail = f"{logStr}-{beforeDay}日{serName}-新增-key:{retentionKey}-{day}日留存量为{retentionKeyNum}"
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = f"insert into analysis_retention_number(retention_num_{day}day,service_key,execute_flag,ref_date) value({retentionKeyNum},'{serId.lower()}',{executeFlag},'{beforeDay}')"
            print(insertSql)
            cur.execute(insertSql)
        else:
            logDetail = f"{logStr}-{beforeDay}日{serName}-更新-{day}日留存量为{retentionKeyNum}"
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = f" update analysis_retention_number t set retention_num_{day}day = {retentionKeyNum} ,update_time = now()  where ref_date = '{beforeDay}' and service_key='{serId}' and execute_flag={executeFlag} "
            print(updateSql)
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

#根据serId 获取服务中文名
def serIdToName(serId):
    if serId =='ZJKJ':
        serName = "早间开机"
    elif serId =='JKZX':
        serName = "健康作息"
    elif serId =='YJYL':
        serName = "夜间音量"
    elif serId =='ZJTX':
        serName = "追剧提醒"
    else:
        serName = "其他"
    return serName

#将指定字符串 dateStr ,偏移 offsetInt 天后再输出字符串
#如 20220805 偏移7天 即增加7天后 输出 20220812
def strToTime(dateStr,offsetInt):
    t = datetime.datetime.strptime(dateStr, "%Y-%m-%d")
    #print(t)
    offset = datetime.timedelta(days=offsetInt)
    re_date = (t+offset).strftime('%Y-%m-%d')
    #print(re_date)
    return  re_date

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 53
        jobName = '数据分析-主动服务-留存量 7日/15日/30日'
        tableName = 'analysis_retention_number'
        # 插入日志表
        saveLogSql = """insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                        value(%s,'%s','%s','%s','%s')""" % (jobId, jobName, detail, tableName, ref_date)
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

def saveRetentionNumMain(executeFlag,day):
    saveRetentionNum(date, 'ZJKJ', executeFlag, day)
    saveRetentionNum(date, 'JKZX', executeFlag, day)
    saveRetentionNum(date, 'YJYL', executeFlag, day)
    saveRetentionNum(date, 'ZJTX', executeFlag, day)

if __name__ == '__main__':
    #today = todayYMD()
    date = getdate(1)

    #today = "2021-12-13"
    print ("%s 日期,执行服务7/15/30留存量报表" % date)
    saveRetentionNumMain(1,7)
    saveRetentionNumMain(1,14)
    saveRetentionNumMain(1,30)
    saveRetentionNumMain(0,7)
    saveRetentionNumMain(0,14)
    saveRetentionNumMain(0,30)

